package com.lhkj.ct.base.utils.file.excel;

import cn.hutool.core.exceptions.ExceptionUtil;
import com.alibaba.fastjson2.JSON;
import com.lhkj.ct.base.enums.ReturnStatus;
import com.lhkj.ct.base.model.ro.ActionResult;
import com.lhkj.ct.base.utils.ServletUtils;
import com.lhkj.ct.base.utils.file.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * <p>
 * excel工具类
 * </p>
 */
public class ExcelUtils {

    private final static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);

    public static void export(Workbook workbook, String fileName) {
        export(workbook,
                fileName,
                ServletUtils.getRequest(),
                ServletUtils.getResponse());
    }

    public static void export(Map<String, Workbook> workbooks, String fileName) {
        HttpServletResponse response = ServletUtils.getResponse();
        // 设置response的编码格式
        response.setCharacterEncoding("UTF-8");
        ZipOutputStream zipOutputStream = null;
        try {
            fileName = FileUtils.encodeFilename(ServletUtils.getRequest(), fileName);
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setContentType("application/octet-stream");
            zipOutputStream = new ZipOutputStream(response.getOutputStream());
            long length = 0;
            for (Map.Entry<String, Workbook> entry : workbooks.entrySet()) {
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                // 将Workbook写入内存流
                if (entry.getValue() instanceof SXSSFWorkbook || entry.getValue() instanceof XSSFWorkbook) {
                    zipOutputStream.putNextEntry(new ZipEntry(entry.getKey() + ".xlsx"));
                } else {
                    zipOutputStream.putNextEntry(new ZipEntry(entry.getKey() + ".xls"));
                }
                entry.getValue().write(baos);
                // 将内存流写入Zip文件
                byte[] b = baos.toByteArray();
                zipOutputStream.write(b);
                length += b.length;
                zipOutputStream.closeEntry();
            }
            response.addHeader("Content-Length", "" + length);
        } catch (IOException e) {
            logger.error("测验导出，excel打包zip异常：" + ExceptionUtil.getMessage(e));
            response.setContentType("application/json;charset=UTF-8");
            response.setStatus(ReturnStatus.SUCCESS.getCode());
            try {
                response.getWriter().write(JSON.toJSONString(ActionResult.fail(ExceptionUtil.getMessage(e))));
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        } finally {
            if (null != zipOutputStream) {
                try {
                    zipOutputStream.flush();
                    zipOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static void export(Workbook workbook, String fileName, HttpServletRequest request, HttpServletResponse response) {
        fileName = FileUtils.encodeFilename(request, fileName);
        OutputStream outputStream = null;
        try {
            //设置ContentType请求信息格式
            if (workbook instanceof SXSSFWorkbook || workbook instanceof XSSFWorkbook) {
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            } else if (workbook instanceof HSSFWorkbook) {
                //设置ContentType请求信息格式
                response.setContentType("application/vnd.ms-excel;charset=utf-8");
            }
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
        } catch (Exception e) {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-type", "application/json;charset=UTF-8");
            response.setStatus(200);
            try {
                response.getWriter().write(JSON.toJSONString(ExceptionUtil.getMessage(e)));
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.flush();
                    outputStream.close();
                }
                if (workbook != null) {
                    workbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void saveExcel(Workbook workbook, String savePath) throws IOException {
        File file = new File(savePath);
        // 判断父目录是否存在， 不存在则创建
        File parent = new File(file.getParent());
        if (!parent.exists()) {
            parent.mkdirs();
        }
        file.createNewFile();
        OutputStream os = new FileOutputStream(file);
        workbook.write(os);
        try {
            os.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param sheet     工作溥
     * @param firstRow  开始行
     * @param lastRow   结束行
     * @param firstCol  开始列
     * @param LastCol   结束列
     * @param cellStyle 单元格样式
     * 批量设置表格样式
     */
    public static void setStyle(Sheet sheet, int firstRow, int lastRow, int firstCol, int LastCol, CellStyle cellStyle) {
        firstRow = Math.max(0, firstRow);
        if (firstRow > lastRow) {
            return;
        }
        if (firstCol > LastCol) {
            return;
        }
        for (int i = firstRow; i <= lastRow; i++) {
            Row row = sheet.getRow(i);
            if (null == row) {
                sheet.createRow(i);
            }
            for (int j = firstCol; j <= LastCol; j++) {
                if (null == sheet.getRow(i).getCell(j)) {
                    sheet.getRow(i).createCell(j);
                }
                sheet.getRow(i).getCell(j).setCellStyle(cellStyle);
            }
        }
    }

//    /**
//     * @param anchor    插入位置和偏移量
//     * @param imagePath 图片路径
//     * @throws IOException 文件读取异常
//     */
//    public static void insertImageToCell(Sheet sheet, ClientAnchor anchor, String imagePath) throws IOException {
//        insertImageToCell(sheet, anchor, ImageUtils.readFile(imagePath), XSSFWorkbook.PICTURE_TYPE_PNG);
//    }

    public static void insertImageToCell(final Sheet sheet, ClientAnchor anchor, byte[] bytes, int pictureType) throws IOException {
        // 利用HSSFPatriarch将图片写入EXCEL
        Drawing<?> patriarch = sheet.createDrawingPatriarch();
        // 插入图片内容
        Picture picture = patriarch.createPicture(anchor, sheet.getWorkbook().addPicture(bytes, pictureType));
        // 图片像素宽度
        int imgWidth = picture.getImageDimension().width;
        // 图片像素高度
        int imgHeight = picture.getImageDimension().height;
        // 单元格像素宽度
        double maxWidth = 0d;
        for (int i = anchor.getCol1(); i < anchor.getCol2(); i++) {
            maxWidth += (sheet.getColumnWidthInPixels(i) - 1);
        }
        double rateX = Math.min(imgWidth / maxWidth, 1.0d);
        // 单元格像素高度
        int maxHeight = (int) (imgHeight + Units.toPoints(anchor.getDy1() + anchor.getDy2()));
        // 行高自适应
        Row row = sheet.getRow(anchor.getRow1());
        row.setHeightInPoints(maxHeight);
        System.out.println((float) Units.pixelToPoints(maxHeight));
        // 图片缩放 v：水平方向 v1：垂直方向
        picture.resize(rateX, imgHeight * 1.0d / maxHeight);
    }

    /**
     * @param sheet  工作溥
     * @param region 合并区域
     * @param border 边框样式
     * 设置表格合并区域和边框
     */
    public static void setRegionBorder(Sheet sheet, CellRangeAddress region, BorderStyle border) {
        setRegionBorder(sheet, region, border, border, border, border);
    }

    public static void setRegionBorder(Sheet sheet, CellRangeAddress region, BorderStyle top, BorderStyle right, BorderStyle bottom, BorderStyle left) {
        if (region.getNumberOfCells() >= 2) {
            sheet.addMergedRegion(region);
        }
        RegionUtil.setBorderTop(top, region, sheet);
        RegionUtil.setBorderRight(right, region, sheet);
        RegionUtil.setBorderBottom(bottom, region, sheet);
        RegionUtil.setBorderLeft(left, region, sheet);
    }

//    /**
//     * 给Cell添加批注
//     *
//     * @param cell   单元格
//     * @param value  批注内容
//     * @param anchor 扩展名
//     */
//    public static void addComment(Cell cell, String value, ClientAnchor anchor) {
//        Sheet sheet = cell.getSheet();
//        cell.removeCellComment();
//        // 关键修改
//        anchor.setDx1(0);
//        anchor.setDx2(0);
//        anchor.setDy1(0);
//        anchor.setDy2(0);
//        anchor.setCol1(cell.getColumnIndex());
//        anchor.setRow1(cell.getRowIndex());
//        anchor.setCol2(cell.getColumnIndex() + 5);
//        anchor.setRow2(cell.getRowIndex() + 6);
//        // 结束
//        Drawing drawing = sheet.createDrawingPatriarch();
//        Comment comment = drawing.createCellComment(anchor);
//        if (anchor instanceof HSSFClientAnchor) {
//            // 输入批注信息
//            comment.setString(new HSSFRichTextString(value));
//            // 将批注添加到单元格对象中
//            cell.setCellComment(comment);
//        } else if (anchor instanceof XSSFClientAnchor) {
//            // 输入批注信息
//            comment.setString(new XSSFRichTextString(value));
//        }
//        // 将批注添加到单元格对象中
//        cell.setCellComment(comment);
//    }

//    //str 是单元格需要放入的 字符串 fontCountInline 是该单元格每行多少个汉字 全角为1 英文或符号为0.5
//    public static float getExcelCellAutoHeight(String str, float fontCountInline) {
//        float defaultRowHeight = 12.00f;//每一行的高度指定
//        float defaultCount = 0.00f;
//        for (int i = 0; i < str.length(); i++) {
//            float ff = getRegex(str.substring(i, i + 1));
//            defaultCount = defaultCount + ff;
//        }
//        return ((int) (defaultCount / fontCountInline) + 1) * defaultRowHeight;//计算
//    }
//
//    public static float getRegex(String charStr) {
//        if (charStr.equals(" ")) {
//            return 0.5f;
//        }
//        // 判断是否为字母或字符
//        if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {
//            return 0.5f;
//        }
//        // 判断是否为全角
//        if (Pattern.compile("[\u4e00-\u9fa5]+$").matcher(charStr).matches()) {
//            return 1.00f;
//        }
//        //全角符号 及中文
//        if (Pattern.compile("[^x00-xff]").matcher(charStr).matches()) {
//            return 1.00f;
//        }
//        return 0.5f;
//    }

    /**
     * 设置单选下拉框
     */
    public static void setRadioValidation(Sheet sheet, DataValidationHelper helper, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        //设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);

        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        //处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }

    /**
     * 日期格式校验
     */
    public static void setDateValidation(Sheet sheet, DataValidationHelper helper, String dataFormat, CellRangeAddressList addressList) {
        //设置验证方式
        DataValidationConstraint constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_EQUAL, "Date(1800, 1, 1)", "", "yyyy-MM-dd");
        //创建验证对象
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        //错误提示信息
        dataValidation.createErrorBox("提示", "请输入[" + dataFormat + "]格式日期");
        // 输入无效值时是否显示错误框
        dataValidation.setShowErrorBox(true);
        // 验证输入数据是否真确
        dataValidation.setSuppressDropDownArrow(true);
        // 设置无效值时 是否弹出提示框
        dataValidation.setShowPromptBox(true);
        //验证和工作簿绑定
        sheet.addValidationData(dataValidation);
    }

    public static void setNumberValidation(Sheet sheet, DataValidationHelper helper, CellRangeAddressList addressList) {
        //设置验证方式
        CellReference cellReference = new CellReference(addressList.getCellRangeAddress(0).getFirstRow(), addressList.getCellRangeAddress(0).getFirstColumn());
        String[] cellRefParts = cellReference.getCellRefParts();
//        System.out.println(cellRefParts[2] + cellRefParts[1]);
        DataValidationConstraint constraint = helper.createCustomConstraint("=ISNUMBER(" + cellRefParts[2] + cellRefParts[1] + ")");
        //创建验证对象
        DataValidation validation = helper.createValidation(constraint, addressList);
        //错误提示信息
        validation.createErrorBox("提示", "输入值类型出错，请输入数值");
        //设置是否显示错误窗口
        validation.setShowErrorBox(true);
        //验证和工作簿绑定
        sheet.addValidationData(validation);
    }

}
